#coding:utf-8
import os
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

path =  os.path.join(
        os.path.dirname(
            os.path.abspath(__file__)
        ),"db_zsgc.sqlite"
    )

app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///"+path
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = True

db = SQLAlchemy(app)

class Person(db.Model):
    id = db.Column(db.Integer,primary_key = True,autoincrement = True)
    name = db.Column(db.String(32))
    age = db.Column(db.Integer)
    gender = db.Column(db.String(8))
    department = db.Column(db.String(32))
    position = db.Column(db.String(32))
    money = db.Column(db.Float)

db.create_all() #执行一次

# 增
# p = Person(
#     name = "曾阿牛",
#     age = 18,
#     gender = "男",
#     department = "明教",
#     position = "CEO",
#     money = "100000"
# )
#
# db.session.add(p)
# db.session.commit()

# p1 = Person(
#     name = "赵敏",
#     age = 18,
#     gender = "女",
#     department = "元朝民兵办事处",
#     position = "处长",
#     money = "1000"
# )
#
# p2 = Person(
#     name = "高圆圆",
#     age = 18,
#     gender = "女",
#     department = "峨眉健身中心",
#     position = "教练",
#     money = "2000"
# )

# p_data = {
#     "name": "小昭",
#     "age": 16,
#     "gender": "女",
#     "department": "明教",
#     "position": "光明顶秘书处处长",
#     "money": "6000"
# }
# p2 = Person(**p_data)
# import random
# first_name = """满王琳王锐王艺博王辰硕王鸿轩王涵润王涵涵王兴王淳曦王雨微王钧涵王浩晏王芊语王乐怡王皓月王文田王文田王文田王禹勋王思卓王国珍王建王天佑王昕王玥婷王浩南王玥雯王天睿王雨辰王靖雯王镜雯王翠楠王镜文王静文王静雯王楠王之骏王子骏王九雏王韬茫王麒鄄王丹王椒勃王泊君王诗议王思馨王誉涵王思思王梦菲王贤博王博琨王复贤王博贤王博毅王博逸王肇博王博儒王傲野王韬韧王傲瑜王傲璇王傲煦王傲昭王韬博王傲琬王傲玥王傲熙王傲漾王傲泠王傲琪王傲钰王傲珂王傲珺王傲瑾王傲琇晗煦王令煦王令璟玥熙汐若瑾皓王珺瑾王文正王傲王傲珣王傲沛王傲汐王傲暄王傲天王傲宸王傲辰王傲玖王志文王傲君王引引王恒昊王耀丹王羽诺王柳诺王易卓王庆和王博宇王继豪王琛杰王沐晴王泽林王琪欧王语萱王又亿王乙苈王梓涛王双富王慧慧王宇涵王崇晟王博韬王淑玲王凯琪王金玲王炣萱王茹涵王茹晞王邵远王少杰王梓萌王乐王晗昱王子萌王健王瑞健王振俨王浩宇王景彤王辰熙王浩淼王逸凡王荣琪王忆晓王思瑶王泽铭王志源王子腾王依然王春凤王熠涵王耀夫王耀弟王庆宇王庆涵王庆超王仪涵王典国王庸淏王庸灏王庸濠王庆通王庸浩王庆松王庆煜王庆王庸泽王庆烁王庆柯王嘉宝王婉婷王炜涵王奕涵王猛王帅军王步伊王绮梦王锡傲王梓芊王玲菊王玲颖王玲嘉王玲琳王玲睿王芳源王芳菲王翰王梓琼王璟雯王凡祯王浩阳王浩晨王静王鸿煊王鸿炫王子涵王道义静王志轩王紫绮王惜春王宇鑫王佳睿鑫王梓皓王书诚王梓浩王浩喆王书芳王一翔王一帆王曦晨王书琴王思颖王钧涵王雅萱王志鹏王璐焓王艺诺王梓炜王艺王燚洲王溢烊王溢阳王一煜王煜一王煜洲王熠洋王熠洲王子曦王一洲王子龙王子旭王燚王玮祯王维彬王祥博王祥闻王祥贵王柄祥王鑫祥王柯鑫王轲鑫王可鑫王金鑫王龙鑫王丙鑫王柄鑫王鑫承王鑫超王舒鑫王书鑫王鑫文王鑫闻王依鑫王永鑫王一鑫王熠晟王鑫龙王鑫揩王立鑫王恒鑫王溢晟王有鑫王一晟王嘉琦王宏伟王鑫泽王博鑫王梓轩王诺鑫王曌鑫王梓诺王一诺王昆仑王一凡杰王熤洲逹王成建王泽鑫王泽王泽维王秉鑫王泽玮王秉印王轩龙王辰皓王辰宇王卓宇王凯旋王晓博王秉福王凯乐王凯泽王秉盛王智晖王书奇王智宇泽王铄曦王珮安王辰俊王辰王智鑫王世贵王博闻王金鹏王贞捷王善祯王智祯王涵畅王智贞王溢州王溢洲王羿洲王果康王利超王子帅王子乐王力超王昌盛王福昌王屹洲王轶洲王漪洲王子印王翊洲王奕洲王韩文王梓王文渤王翰文王文博王一王子辰王小雅王子轩王子晟王玲王熙媛王强王云迪王懿洲王乙洲王子昂王梓濠王思彤王子濠王子浩王骏驰王子欣王梦琪王濠王一州王宇灿王文凯王腾锐王一童王文雅王文龙王鹳丁王家鑫王曦涵王曦熙浩王浩蕊王浩瀚王曦浩王乙又王浩轩王涵蕊王浩琪琪王天琪王浩天王记龙王昆王瑞晓龙王英王思栋王家一王倩倩王志俊熙王羲泽王曦泽王力可王雨琪王宁王曦之王鑫磊王艺涵王其韦王天乐王梓涵王升沐王昊王跃丽王奥洁曦王曦哲王辉王扬俊王子烁王宇泽王乙署王添乐王旭炎王浩初王学识王学可王琪岩王一好王旭阳王贞尔王子睿王瀚文王金宝王旭升王一嘉王双喜王欣悅王敏鑫王若曦王梓骁王子芸王大维王子婷王维王炳瑞王烁王孜一王懿轩王昭熠王海波王子晗涵王佳烨王永刚王汐越王舜天王婉莲王子宁王晓雅王雨涵王欣淼王雪嫚王昊宇王冰洋王孟涵王孟涵王子灏王澡厉王梦璐王晓东王夫颢王丽威王欣然王若熙王锦轩王嘉悦王若汐王瑞鑫王梓鸣王珺琪王雪婷王玉珩王王跃勤王余泽王筠酿王余匀王彬严王少励王志玮王志铨王吕儿王吕媛王枭雄王承基王烁雅王亦吕王金雅王云霞王春容王春桥王云科王世明王莉莉王昊鑫王昊烨王昊泽王浩泽王子淳王聃蘅王熙荣王旭尧王吾骥王凤林王博王雪枫王嘉懿王怡怿王婉茹王润泽王莘钰王昕玥王煜博王妍丁王语霖王语琳王琳玥王琳娜王钰琳王雨嫣王羽嫣王语嫣王伊琳王浩祥王玉洁王麒王艺洋王安迪王语涵王功臣王绮涵王绮瑶王烁焱王昊辰王昊阳王景龙王芳瑜王子溢王嘉溢王梓暄王朝瑞王苑贝王婉谨王一轩王令达王泽明王腾云王海伦王天正王中永王蕊王建辉王海燕王紫玉王建全王山坡王愈心王茂洪王永辉王成博王智阳王子鑫王歌贸王玺晨王哲轩王子瑜王麒皓王禹淇王采萤王紫王吴宇王浩雁王浩彦王懿瑄王浩赝王子王浩跃王浩偃王浩偃王浩熠王浩昱王浩嘉王浩宸王浩瑞王浩晖王学斐王瑞玲王国栋王雪斐王雪飞王浩宴王浩裕王浩裕王浩渝王允昊王明泽王铧埭王泽龙王嘉泽王鸿富王昊明王江定王尚王昶王尚恩王思恩王成果王浩明王明浩王雨嘉王紫萱王琪涵王启涵王凌薇王浩辰王鹏王妙可王浩萌王立恒王煜恒王昱恒王利恒王子恒王恒王洋溢王韵宇王韵宇王鑫王瀚霖王鸿亮王诗晗王蓉王智辉王月梅王齐财王颢其王荐琰王欲还王观潭王党民王发凯王岩颛王谱臣宽王绪喾王浩荣王熙王宽栌王哉象王治晰王有强王志杰王子薇王颖娜王平王海龙王磊磊王保林王若夕王梓骏王骏轩王若菲轩王坤王煦王文萱王宇阳王竞飞王诗琪王鸿哲王耀戊王炳丁王溪王金凤王浩博王昊天王嘉烨王子晨王卓王庭东王清晨王逸飞王清王世龙王清昊王佳昊王世博王佳烁王若晗王翔王骏逸王美娟王佳旭王佳浩王清浩王庭海王浩哲王晶晶王苗王锦娟王露凝王淳王世洋王嘉乐轩王雨泽王沛軒婕溪王贤耀宇王嘉瑞王贤锋王贤祥王贤光王贤若王贤军王亦晨王贤翔王隆茕王浩盟王贤添王贤华王贤龙王贤意王鑫鹏王山懿王志宸王智宸王梓臣王子妍王妍玥王志盟王昊妍王昊萌王玥萌王智萌王阳怡王志萌王阳溢王思语王思涵王一湾王辰尧王冰王泞王濛溪王欢王诗语王雨诺王子悠王洁宇王耀葳王凯漾王凯悦王凯皓王紫阳王紫贤王梓蕴王泽桐王驿岢王旭王苹凱王思睿王邀晨王梓镐王译晃王传磊王可昕王馨康王婉圩王传铭王传宇王传钧王传豪王传毅王传雨王传煦王传蓄王传胜王常皓王轶群王天骐王清海王世华杰王璐萌王骁骁王晓晓王传昊王传博王传霖王笑笑王冰冰王朱娴王逸霏王得霞王云龙王静睿王传鑫王传泽王奕琳王传智王宇浩王杰颢王传武浩王传硕王鑫浩王传瑞王传航王传鸿王传震王世鸾王红亮王亭婕王国祥王亭芳王子芳王签循王鲸诋王签哲王茁灵王希嘉王攀巽王童王涛王泽云王晗宇王琦王本鸾王润龙王嘉帅王紫茜王家利王立琪王洋王晟江王崔玲王浩文王奕轩王志斌王烨瑶王珂王燕王艺霖王雅晗王晗雨王羿朝王耔怡王佳彤王国华王怡耔王贞彤王国辉王国旗王琦彤王珺彤王卓辰王嘉彤王悦彤王倩王彬王德论王砻王靖暄王堂砾王后竣王后棕语昊欣晞王海洋王洋淼王王杺王以佳王思亮王远泽王海博王佳琦之博昊王兵王梦涛王型雳王昶己王铭泽王林王安苹王景伟王韵晴王瑾轩王越晨王辰雨王子禹王立浚王立骏王晓娜王辰泊王雨婷王焱雨王浩楠王骁羽王云汐王玄昱王烁婷王知一王一珂王佳悦王馨怡王郑王瑛王于燊王沐辰王玮瑜王瑛淏王荣王绅王绅淏王祥淏王嘉淏王申淏王绅灏王昌淏王朋淏王振鸣王树朋王锦鹏王柯豫王碧媛伟王红伟王祥业王诗妙王芊茹君王妙萁王妙王泽民王泽扬王泽洋王柯萌王昊然王萌昊王妍萌王嘉怡王小冉王晗玥王潘潘王战王芯蕾博王腾君王志宇王邦权王昭雪王鹳力王杰博王宇顺""".split("王")
# last_name = """赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方"""
# department_list = """白驼山、桃花岛、古墓派、少林、武当、全真派、峨嵋、崆峒、南拳门、丐帮、逍遥派、明教、日月神教、白莲教、红花会""".split("、")
#
# for i in range(10000):
#     p = Person(
#             name = random.choice(last_name)+random.choice(first_name),
#             age = random.randint(18,30),
#             gender = random.choice(["女","男"]),
#             department = random.choice(department_list),
#             position = random.choice(["掌门","长老","护法","弟子"]),
#             money = random.randint(8000,100000)
#         )
#     db.session.add(p)
#     db.session.commit()

# 查
    #查所有
# p_list = Person.query.all()
# for p in p_list:
#     print(p.name)
    #查所有丐帮弟子
# p_list = Person.query.filter() #默认查所有
#p_list = Person.query.filter(Person.department == "丐帮") #默认查所有
#p_list = Person.query.filter_by(department = "丐帮")
#查询年龄大于20岁的员工
# p_list = Person.query.filter(Person.age > 20) #默认查所有
#查询明教所有20岁的女生
# query = {
#     "department": "明教",
#     "age": 20,
#     "gender": "女"
# }
# p_list = Person.query.filter_by(**query)

#like模糊查询
# p_list = Person.query.filter(Person.name.like("%一%"))

#返回20条数据,限制条数
# p_list = Person.query.filter().limit(3)

#偏移
# p_list = Person.query.filter().limit(3).offset(6)
#page = 1   page_size = 10   limit 10    offset 0
#page = 2   page_size = 10   limit 10    offset 10
#page = 3   page_size = 10   limit 10    offset 20
#page = n   page_size = 10   limit 10    offset page_size*(n-1)

#排序
# p_list = Person.query.filter_by(department = "桃花岛").order_by(Person.age)
#p_list = Person.query.filter_by(department = "桃花岛").order_by(Person.age.desc())

# for p in p_list:
#     print(p.id,p.name,p.department,p.age)

#get
# p = Person.query.get(1000000000000) #查id为1，只能以id查
# print(p.id,p.name,p.department,p.age,p.gender)
#first
# query = {
#     "department": "明教",
#     "age": 20,
#     "gender": "女"
# }
# p = Person.query.filter_by(**query).order_by(Person.id).first()
# print(p.id,p.name,p.department,p.age,p.gender)

#聚合查询
    # max min count sum avg
# from sqlalchemy import func
# #查询明教女生的个数
# query = {
#     "department": "明教",
#     "gender": "女"
# }
# result = db.session.query(
#     Person.department,func.count(Person.id)
# ).filter_by(
#     **query
# ).all()
# print(result)
#查询明教女生的平均年龄
# query = {
#     "department": "明教",
#     "gender": "女"
# }
# result = db.session.query(
#     Person.department,func.avg(Person.age)
# ).filter_by(
#     **query
# ).all()
# print(result)

#分组查询
# from sqlalchemy import func
# #每个门派弟子的平均年龄
# result = db.session.query(
#     Person.department,func.avg(Person.age)
# ).group_by(
#     Person.department
# ).all()
# print(result)

#逻辑查询
# from sqlalchemy import and_,or_,not_
#查询丐帮或者明教弟子
# p_list = Person.query.filter(
#     not_(
#         Person.department == "丐帮",
#     ),
#     not_(
#         Person.department == "明教"
#     ),
#     Person.gender == "男"
#
# )
#
# for p in p_list:
#     print(p.id,p.name,p.department,p.age,p.gender)

# 删
# p = Person.query.get(9967)
# print(p.id,p.name,p.department,p.age,p.gender)
# db.session.delete(p)
# db.session.commit()
# 改
# p = Person.query.get(9967)
# p.name = "蒋郑华"
# db.session.commit()







